/**
 * 
 */
package org.shortestpath.service.dao;

import org.shortestpath.core.Point;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import org.postgis.Geometry;
import org.postgis.LineString;
import org.postgis.MultiLineString;
import org.postgis.PGgeometry;

/**
 * @author Thilani
 * 
 */
public class DbDao {
	
	private Connection conn = null;

	/*
	 * Table names 
	 */
	private static final String DYNAMIC_TABLE_NAME = "dynamicTable";
	public static final String SIMPLE_TABLE_NAME = "simpleroads22";
	public static final String ROADS_TABLE_NAME = "road3";
	
	public DbDao(){
		initConnection();
	}
	
	/**
	 * Load the JDBC driver and establish a connection and 
	 * add the geometry types to the connection.
	 * 
	 * @param hostName
	 * @param port
	 * @param db
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Connection createPgSqlConnection(String hostName, String port, String db, String uname, String password) 
		throws ClassNotFoundException, SQLException {

		Class.forName("org.postgresql.Driver");
		String url = "jdbc:postgresql://"+ hostName + ":" + port + "/" + db ;
		java.sql.Connection conn = DriverManager.getConnection(url, uname, password);

		((org.postgresql.PGConnection) conn).addDataType("geometry", "org.postgis.PGgeometry");
		((org.postgresql.PGConnection) conn).addDataType("box3d", "org.postgis.PGbox3d");
		
		return conn;
	}
	
	private Connection initConnection(){		
		try {
			conn = createPgSqlConnection("localhost", "5432", "srilanka", "postgres", "102@yahoo.com");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getAllRoads() throws SQLException{
		return executeOnDb("select the_geom as geom,gid, road_id from " + ROADS_TABLE_NAME);
	}
	
	/**
	 * 
	 * @param geometry
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getOverlapGeoms(Geometry geometry) throws SQLException{
		return executeOnDb("select the_geom as geom,gid from " + ROADS_TABLE_NAME + " where near to geometry");
	}
	
	/**
	 * 
	 * @param geometry
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getAdjacentVertexes(PGgeometry geometry) throws SQLException{
		return executeOnDb("select the_geom as geom,gid from " + ROADS_TABLE_NAME + " where geometry = " + geometry);
	}

    /**
	 * Returns the adjacent vertexes for a given vertex
	 * @param point
	 * 		The given vertex
	 * @return
	 * 		Returns the ResultSet which contains adjacent vertexes
	 * @throws SQLException
	 */
	public ResultSet getAdjacentVertexes(Point point) throws SQLException{
		return executeOnDb("select the_geom as geom,gid from " + ROADS_TABLE_NAME + " where geometry = " + point);
	}

	public void createSimpleTable(String sql) throws SQLException{
		Statement s = conn.createStatement();
		s.executeUpdate(sql);
	}

	public void setSimpletableData(String sql) throws SQLException{
		Statement s = conn.createStatement();
		s.executeUpdate(sql);
	}

	public ResultSet getAllPoints() throws SQLException {
		return executeOnDb("select x_cor as x, y_cor as y, gid, road_id, adj_nodes from " + SIMPLE_TABLE_NAME + " ORDER BY gid ASC");
	}

	/**
	 * Calculates the adjacent vertexes for a given vertex.
	 *     1. Get all the geometries for roads and calculates geometry segments relative to existing adj points
	 *     2. Check whether geometries and geometry segments intersects
	 *     3. If intersects recalculate the adjacent vertexes for the given vertex and insert that vertex to the table
	 *
	 * @param point
	 * @param adjNodes
	 * @return
	 * @throws SQLException
	 */
	public Collection<Point> getStIntersection(Point point, String adjNodes, String simpleTableName, String roadsTableName) throws SQLException{
		Collection<Point> intersections = new ArrayList<Point>();
		//Get the array of adjacent nodes for iteration
		String[] adjacentNodes = adjNodes.split(",");
		
		// 1. Get all the geometries for roads table
		ResultSet resultSet = executeOnDb("select the_geom, road_id from " + roadsTableName);
		while (resultSet.next()) {
			PGgeometry geom = (PGgeometry) resultSet.getObject(1);
			int roadId = Integer.parseInt(resultSet.getObject(2).toString());
			//1. calculates geometry segments relative to existing adj points
			for (int i = 0; i < adjacentNodes.length; i++) {
	            String adjacentNode = adjacentNodes[i];
	            if (!adjacentNode.equals("-1")) {
	                ResultSet resultSetAdjNodes = executeOnDb("select x_cor as x, y_cor as y, gid, road_id from "
	                        + simpleTableName + " where gid = " + adjacentNode);
	                while (resultSetAdjNodes.next()) {
	                    double xCore = (Double) resultSetAdjNodes.getObject(1);
	                    double yCore = (Double) resultSetAdjNodes.getObject(2);
	                    Point adjPoint  = new Point(xCore, yCore);

	                    //Create geometry for the given point and the adjacent node (point vs adj_node geometry).
	                    //This should be a straight line.
	                    org.postgis.Point[] twoPoints = {new Point(point.getX(), point.getY()), adjPoint};
	                    LineString simpleLineString = new LineString(twoPoints);
	                    LineString[] simpleLineStrings = new LineString[1];
	                    simpleLineStrings[0] = simpleLineString;
	                    MultiLineString simpleMultiLineString = new MultiLineString(simpleLineStrings);
	                    PGgeometry simplePGgeometry = new PGgeometry(simpleMultiLineString);

	                    //2. Check whether point vs adj_node geometry and road table geometry segment intersects
	                    ResultSet resultSetIntersect = executeOnDb("select ST_Intersection(ST_GeomFromText('"
	                            + simplePGgeometry.getGeometry() + "'), " + "ST_GeomFromText('" + geom.getGeometry() + "'))");
	                    while (resultSetIntersect.next()) {
	                        //escape if the geometry is in the same road
	                        if (point.getRoadId() != roadId) {
	                            PGgeometry intersectGeom = (PGgeometry) resultSetIntersect.getObject(1);
	                            if (!"GEOMETRYCOLLECTION EMPTY".equals(intersectGeom.getValue())) {
	                                if (intersectGeom.getType().equalsIgnoreCase("geometry")) {
	                                    performIntersection(point, simpleTableName, adjacentNodes, i, intersectGeom);
	                                }
	                            }
	                        }
	                    }
	                }
	            }
	        }

		}
		Collections.sort((List<Point>) intersections, new Comparer());
		return intersections;
	}

    private void performIntersection(Point point, String simpleTableName, String[] adjacentNodes, int i, PGgeometry intersectGeom) throws SQLException {
        //ResultSet resultSetMaxGid = executeOnDb("select MAX(gid) as maxgid from " + simpleTableName);
        //while (resultSetMaxGid.next()) {
         //   int maxGid = (Integer) resultSetMaxGid.getObject(1) + 1;

            //check whether the intersect point is already in the table
            boolean hasSameIntersectVertex = false;

            if (intersectGeom.getGeometry() instanceof org.postgis.Point) {
                setIntersectNodes(point, simpleTableName, adjacentNodes, i, (org.postgis.Point)intersectGeom.getGeometry());
            } else if (intersectGeom.getGeometry() instanceof org.postgis.MultiPoint) {
                org.postgis.MultiPoint intersectMultiPoints  = (org.postgis.MultiPoint) intersectGeom.getGeometry();
                org.postgis.Point[] intersectPoints  = intersectMultiPoints.getPoints();
                for (org.postgis.Point intersectPoint : intersectPoints) {
                    setIntersectNodes(point, simpleTableName, adjacentNodes, i, intersectPoint);
                }
            } else {
                System.out.println("This type is not coverd " + intersectGeom.getGeometry().getClass());
            }
       // }
    }

    private void setIntersectNodes(Point point, String simpleTableName, String[] adjacentNodes, int i, org.postgis.Point intersectPoint) throws SQLException {
        boolean hasSameIntersectVertex = false;
        int intersectGid = 0;
        // If the intersect point is same as the given point then no need to add it to the adjacent vertexes
        if ((point.getX() == intersectPoint.getX()) && (point.getY() == intersectPoint.getY())) {
            return;
        }
        ResultSet resultSetInPoint  = executeOnDb("select gid as intersectGid from " + simpleTableName + " where x_cor = "
                + intersectPoint.getX() + "" + "and y_cor =" + intersectPoint.getY());
        while (resultSetInPoint.next()) {
            hasSameIntersectVertex = true;
            intersectGid = (Integer) resultSetInPoint.getObject(1);
            
            // 3. If intersects recalculate the adjacent vertexes for the given vertex and insert that vertex to the table
            adjacentNodes[i] = Integer.toString(intersectGid);
            String newAdjNodes = "";
            for (int j = 0; j < adjacentNodes.length; j++) {
                newAdjNodes = newAdjNodes + adjacentNodes[j] + ",";
            }
            
            String insertTable = "UPDATE " + simpleTableName + " SET adj_nodes = "
            + "'" + newAdjNodes + "' WHERE x_cor = " + point.getX() +" and y_cor = " + point.getY() + ";";
            setSimpletableData(insertTable);
            
            return;
        }
        
        // If there is no vertex match for intersect vertex then insert new vertex and point to it.
        if (! hasSameIntersectVertex) {
            double intersectX = intersectPoint.getX();
            double intersectY = intersectPoint.getY();
        }
    }
	
	private void setIntersectPoints(){
	    
	}
	
	public ResultSet executeOnDb(String sql) throws SQLException{
		Statement s = conn.createStatement();
		return s.executeQuery(sql);
	}
	
	
	private void setAdjVertices(List<Point> point, int pointId, int roadId){
	    
	    
	}
	
	/**
	 * 
	 * Get the vertex for the given gid
	 * 
	 * @param gid
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getVertex(int gid) throws SQLException{
	    return executeOnDb("select gid, road_id, x_cor, y_cor from " + SIMPLE_TABLE_NAME);
	}
	
	/**
	 * The comparer class for sort vector against the distance
	 * 
	 * @author Thilani
	 *
	 */
	class Comparer implements Comparator {
	    public int compare(Object obj1, Object obj2)
	    {
	        double i1 = ((Point)obj1).getAdj_distance();
	        double i2 = ((Point)obj2).getAdj_distance();
	        return (int) ((Math.abs(i1) - Math.abs(i2))*1000);
        }
    }

    /**
     *
     * @param vertex
     * @return
     * @throws SQLException
     */
    public String generateDynamicTable(Geometry geom, int gid) throws SQLException {
        //TODO check whether dynamic table is exists and if not create a new one
        Statement s = conn.createStatement();
        s.executeUpdate("DELETE FROM " + DYNAMIC_TABLE_NAME);
        String statement = "INSERT INTO " + DYNAMIC_TABLE_NAME + " (gid, geom) VALUES " +
        		"("+gid+" ,ST_GeomFromText('" + geom + "'))";
        
        s.executeUpdate(statement);
        return DYNAMIC_TABLE_NAME;
    }
}
